package com.bjy.qa.dao.manage;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface CleanDataDao {
    /**
     * 得到所有表的 schema
     * @return
     */
    @Select("SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA=(SELECT DATABASE())")
    List<Map> getAllTableSchema();

    /**
     * 清理已删除的数据（两天前删除的数据）
     * @param tableName 表名
     * @return
     */
    @Update("DELETE FROM `${tableName}` WHERE is_delete = true AND updated_at < DATE_SUB(NOW(),INTERVAL 2 day);" +
            "OPTIMIZE TABLE `${tableName}`;")
    int cleanDeleted(@Param("tableName") String tableName);

    /**
     * 清理 测试结果详情表 ft_test_result_step
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   ft_test_result_step " +
            "WHERE" +
            "   rid IN (" +
            "   SELECT" +
            "       id " +
            "   FROM" +
            "       ft_test_result_suite " +
            "   WHERE" +
            "       reserved != true AND " +
            "       created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM ft_test_suite WHERE case_type = 3 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>) " +
            "   )" +
            "</script>")
    int cleanTestResultStep(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 测试用例结果表 ft_test_result_case
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   ft_test_result_case " +
            "WHERE" +
            "   rid IN (" +
            "   SELECT" +
            "       id " +
            "   FROM" +
            "       ft_test_result_suite " +
            "   WHERE" +
            "       reserved != true AND " +
            "       created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM ft_test_suite WHERE case_type = 3 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>) " +
            "   )" +
            "</script>")
    int cleanTestResultCase(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 测试结果表 ft_test_result_suite
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   ft_test_result_suite " +
            "WHERE" +
            "   reserved != true AND " +
            "   created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM ft_test_suite WHERE case_type = 3 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>)" +
            "</script>")
    int cleanTestResultSuite(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 悬空（没有测试套件）的测试结果详情表 ft_test_result_step
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   ft_test_result_step " +
            "WHERE" +
            "   rid IN (" +
            "   SELECT" +
            "       ft_test_result_suite.id " +
            "   FROM" +
            "       ft_test_result_suite" +
            "       LEFT JOIN ft_test_suite ON ft_test_result_suite.suite_id = ft_test_suite.id " +
            "   WHERE" +
            "   ISNULL( ft_test_suite.id ) " +
            "   );")
    int cleanHangAirTestResultStep();

    /**
     * 清理 悬空（没有测试套件）的测试用例结果表 ft_test_result_case
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   ft_test_result_case " +
            "WHERE" +
            "   rid IN (" +
            "   SELECT" +
            "       ft_test_result_suite.id " +
            "   FROM" +
            "       ft_test_result_suite" +
            "       LEFT JOIN ft_test_suite ON ft_test_result_suite.suite_id = ft_test_suite.id " +
            "   WHERE" +
            "   ISNULL( ft_test_suite.id ) " +
            "   );")
    int cleanHangAirTestResultCase();

    /**
     * 清理 悬空（没有测试套件）的测试结果表 ft_test_result_suite
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   ft_test_result_suite " +
            "WHERE" +
            "   id IN (" +
            "   SELECT" +
            "       ftrs.id " +
            "   FROM" +
            "       (" +
            "       SELECT" +
            "           ft_test_result_suite.id " +
            "       FROM" +
            "           ft_test_result_suite" +
            "           LEFT JOIN ft_test_suite ON ft_test_result_suite.suite_id = ft_test_suite.id " +
            "       WHERE" +
            "       ISNULL( ft_test_suite.id )) ftrs " +
            "   );")
    int cleanHangAirTestResultSuite();

    /**
     * 清理 性能测试结果日志表 pt_test_result_log
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   pt_test_result_log " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       id " +
            "   FROM" +
            "       pt_test_result " +
            "   WHERE" +
            "       base_line != true AND " +
            "       created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM pt_test_suite WHERE case_type = 5 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>) " +
            "   )" +
            "</script>")
    int cleanPTTestResultLog(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 性能测试结果报告 pt_test_result_report
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   pt_test_result_report " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       id " +
            "   FROM" +
            "       pt_test_result " +
            "   WHERE" +
            "       base_line != true AND " +
            "       created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM pt_test_suite WHERE case_type = 5 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>) " +
            "   )" +
            "</script>")
    int cleanPTTestResultReport(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 性能测试结果 对应 性能测试脚本 表 pt_test_result_test_script
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   pt_test_result_test_script " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       id " +
            "   FROM" +
            "       pt_test_result " +
            "   WHERE" +
            "       base_line != true AND " +
            "       created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM pt_test_suite WHERE case_type = 5 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>) " +
            "   )" +
            "</script>")
    int cleanPTTestResultTestScript(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 性能测试结果表 pt_test_result
     * @param notice true: 删除发钉钉通知的数据， false: 删除不发钉钉通知的数据
     * @param day 多少天前的数据
     * @return
     */
    @Update("<script>" +
            "DELETE " +
            "FROM" +
            "   pt_test_result " +
            "WHERE" +
            "   base_line != true AND " +
            "   created_at &lt; DATE_SUB( NOW(), INTERVAL ${day} DAY ) " +
            "   AND suite_id IN ( SELECT id FROM pt_test_suite WHERE case_type = 5 <when test='notice'> AND robot_id &gt; 0 </when> <when test='!notice'> AND robot_id = 0 </when>)" +
            "</script>")
    int cleanPTTestResult(@Param("notice") boolean notice, @Param("day") int day);

    /**
     * 清理 悬空（没有测试套件）性能测试结果日志表 pt_test_result_log
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   pt_test_result_log " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       pt_test_result.id " +
            "   FROM" +
            "       pt_test_result" +
            "       LEFT JOIN pt_test_suite ON pt_test_result.suite_id = pt_test_suite.id " +
            "   WHERE" +
            "   ISNULL( pt_test_suite.id ) " +
            "   );")
    int cleanHangAirPTTestResultLog();

    /**
     * 清理 悬空（没有测试套件）性能测试结果报告 pt_test_result_report
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   pt_test_result_report " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       pt_test_result.id " +
            "   FROM" +
            "       pt_test_result" +
            "       LEFT JOIN pt_test_suite ON pt_test_result.suite_id = pt_test_suite.id " +
            "   WHERE" +
            "   ISNULL( pt_test_suite.id ) " +
            "   );")
    int cleanHangAirPTTestResultReport();

    /**
     * 清理 悬空（没有测试套件）性能测试结果 对应 性能测试脚本 表 pt_test_result_test_script
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   pt_test_result_test_script " +
            "WHERE" +
            "   result_id IN (" +
            "   SELECT" +
            "       pt_test_result.id " +
            "   FROM" +
            "       pt_test_result" +
            "       LEFT JOIN pt_test_suite ON pt_test_result.suite_id = pt_test_suite.id " +
            "   WHERE" +
            "   ISNULL( pt_test_suite.id ) " +
            "   );")
    int cleanHangAirPTTestResultTestScript();

    /**
     * 清理 悬空（没有测试套件）性能测试结果表 pt_test_result
     * @return
     */
    @Update("DELETE " +
            "FROM" +
            "   pt_test_result " +
            "WHERE" +
            "   id IN (" +
            "   SELECT" +
            "       ptrs.id " +
            "   FROM" +
            "       (" +
            "       SELECT" +
            "           pt_test_result.id " +
            "       FROM" +
            "           pt_test_result" +
            "           LEFT JOIN pt_test_suite ON pt_test_result.suite_id = pt_test_suite.id " +
            "       WHERE" +
            "       ISNULL( pt_test_suite.id )) ptrs " +
            "   );")
    int cleanHangAirPTTestResult();

    /**
     * 释放 ft_test_result_step 表空间
     * @return
     */
    @Update("OPTIMIZE TABLE ft_test_result_step;")
    int optimizeTableTestResultStep();

    /**
     * 释放 ft_test_result_case 表空间
     * @return
     */
    @Update("OPTIMIZE TABLE ft_test_result_case;")
    int optimizeTableTestResultCase();

    /**
     * 释放 ft_test_result_suite 表空间
     * @return
     */
    @Update("OPTIMIZE TABLE ft_test_result_suite;")
    int optimizeTableTestResultSuite();
}
